/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package Bean;

import Objects.Album;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import javax.ejb.Stateful;

/**
 *
 * @author ntfsbk
 */
@Stateful(mappedName = "uploadBean")
public class uploadBean implements uploadBeanRemote {

    String connectionString = "jdbc:sqlserver://NTFSBK-PC\\SQLEXPRESS_R2;"
            + "databaseName=imageSite";
    String serverUserName = "sa";
    String serverPasswd = "123456";
    boolean createdNew = false;
    int albumId;
    String tempName=null;
    int tempNewAlbumId;

    @Override
    public int getNextImageId() {
        int nextId = -1;
        try {
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
            Connection conn = DriverManager.getConnection(connectionString, serverUserName, serverPasswd);

            PreparedStatement pstm = conn.prepareStatement("SELECT max(imageId) as nextId FROM Images");

            ResultSet rs = pstm.executeQuery();

            rs.next();
            String next = rs.getString("nextId");
            if (next == null) {
                return 1;
            }
            nextId = Integer.parseInt(next);

            pstm = conn.prepareStatement("SELECT link as link FROM Images WHERE imageId=?");
            pstm.setInt(1, nextId);
            rs = pstm.executeQuery();
            rs.next();
            String link = rs.getString("link");
            nextId = Integer.parseInt(getpostfix(link));
            nextId++;
            pstm.close();
            return nextId;
        } catch (Exception e) {
            return nextId;
            //throw e;
        }
    }

    // Add business logic below. (Right-click in editor and choose
    // "Insert Code > Add Business Method")
    @Override
    public List<Album> getListAlbum(int userId) {
        List<Album> albums = new ArrayList<Album>();
        try {
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
            Connection conn = DriverManager.getConnection(connectionString, serverUserName, serverPasswd);

            PreparedStatement pstm = conn.prepareStatement("SELECT  * FROM Albums WHERE userId=?");
            pstm.setInt(1, userId);
            ResultSet rs = pstm.executeQuery();

            while (rs.next()) {
                int albumId = rs.getInt("albumId");
                String albumName = rs.getString("albumName");
                int userid = rs.getInt("userId");
                String date = rs.getString("dateCreate");
                String thumnail = rs.getString("thumbnail");
                albums.add(new Album(albumId, albumName, userid, date, thumnail));
            }
            pstm.close();
            return albums;
        } catch (Exception e) {
            return albums;
            //throw e;
        }
    }

    @Override
    public int insertNewAlbum(String albumName, int userId, String createdDate) {
        int id = -1;
        try {
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
            Connection conn = DriverManager.getConnection(connectionString, serverUserName, serverPasswd);

            PreparedStatement pstm = conn.prepareStatement("INSERT INTO Albums(albumName,userId,dateCreate) VALUES(?,?,?)");
            pstm.setString(1, albumName);
            pstm.setInt(2, userId);
            pstm.setString(3, createdDate);
            pstm.executeUpdate();

            pstm = conn.prepareStatement("select max(albumId) as id from Albums");
            ResultSet rs = pstm.executeQuery();

            while (rs.next()) {
                id = Integer.parseInt(rs.getString("id"));
            }
            pstm.close();
            return id;
        } catch (Exception e) {
            return id;
        }
    }

    @Override
    public void deleteAlbum(int id) throws Exception {
        try {
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
            Connection conn = DriverManager.getConnection(connectionString, serverUserName, serverPasswd);

            PreparedStatement pstm = conn.prepareStatement("delete from Albums where albumId=?");
            pstm.setInt(1, id);
            pstm.executeUpdate();

            pstm.close();
        } catch (Exception e) {
            throw e;
        }
    }

    @Override
    public synchronized String insertNewImage(String folder, int userId, String suf) throws Exception {
        boolean test=true;
        System.out.println("test 139:"+createdNew);
        try {            
            if (createdNew == false) {
                test=true;
                String newAlbumName = getNewNameAlbum();
                Date calendar = new Date();
                int year = calendar.getYear() + 1990;
                int month = calendar.getMonth();
                int date = calendar.getDate();
                String createdDate = year + "-" + month + "-" + date;
                albumId = insertNewAlbum(newAlbumName, userId, createdDate);
                createdNew = true;
                String fileName = getNextImageId() + "." + suf;
                String link = folder + "/" + fileName;
                System.out.println("test 152:link:"+link);
                insertImage(albumId, link);
                System.out.println("test 154:albumId:"+albumId);
                updateThumbnail(link, albumId);
                return fileName;
            } else {
                test=false;
                String fileName = getNextImageId() + "." + suf;
                String link = folder + "/" + fileName;
                insertImage(albumId, link);
                updateThumbnail(link, albumId);
                return fileName;
            }
        } catch (Exception e) {
            System.out.println(createdNew);
            System.out.println(albumId);
            System.out.println(test);
            throw e;
        }
    }

    private void insertImage(int albumId, String link) throws Exception {
        try {
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
            Connection conn = DriverManager.getConnection(connectionString, serverUserName, serverPasswd);

            PreparedStatement pstm = conn.prepareStatement("INSERT INTO Images(albumId,link)VALUES (?,?)");
            pstm.setInt(1, albumId);
            pstm.setString(2, link);
            pstm.executeUpdate();

            pstm.close();
        } catch (Exception e) {
            throw e;
        }
    }

    private String getNewNameAlbum() {
        Date calendar = new Date();
        int tempMonth = calendar.getMonth();
        String month = "";
        switch (tempMonth) {
            case 1:
                month = "January";
            case 2:
                month = "February";
            case 3:
                month = "March";
            case 4:
                month = "April";
            case 5:
                month = "May";
            case 6:
                month = "June";
            case 7:
                month = "July";
            case 8:
                month = "August";
            case 9:
                month = "September";
            case 10:
                month = "October";
            case 11:
                month = "November";
            case 12:
                month = "December";
        }
        int date = calendar.getDate();
        int year = calendar.getYear() + 1900;

        String nameAlbum = month + " " + date + ", " + year;
        return nameAlbum;
    }

    private void updateThumbnail(String link, int albumId) throws Exception {
        try {
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
            Connection conn = DriverManager.getConnection(connectionString, serverUserName, serverPasswd);

            PreparedStatement pstm = conn.prepareStatement("UPDATE Albums SET thumbnail=? WHERE albumId=?");
            pstm.setString(1, link);
            pstm.setInt(2, albumId);
            pstm.executeUpdate();

            pstm.close();
        } catch (Exception e) {
            throw e;
        }
    }

    public boolean checkEmptyAlbum(int albumId) throws Exception {
        try {
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
            Connection conn = DriverManager.getConnection(connectionString, serverUserName, serverPasswd);

            PreparedStatement pstm = conn.prepareStatement("SELECT count(*)as number FROM Albums,Images WHERE Albums.albumId=? AND Albums.albumId=Images.albumId");
            pstm.setInt(1, albumId);
            ResultSet rs = pstm.executeQuery();

            rs.next();
            int number = Integer.parseInt(rs.getString("number"));

            pstm.close();
            if (number > 0) {
                return false;
            } else {
                return true;
            }
        } catch (Exception e) {
            throw e;
        }
    }

    private String getpostfix(String link) {
        String postfix = "";
        int pre = link.lastIndexOf("/");
        int pos = link.lastIndexOf('.');
        if (pos > 0 && pos < link.length() - 1) {
            postfix = link.substring(pre + 1, pos);
        }
        System.out.println("postfix: " + postfix);
        return postfix;
    }

    @Override
    public void deleteImage(String imageLink) throws Exception {
        try {
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
            Connection conn = DriverManager.getConnection(connectionString, serverUserName, serverPasswd);

            PreparedStatement pstm = conn.prepareStatement("SELECT * FROM Images WHERE link=?");
            pstm.setString(1, imageLink);
            ResultSet rs = pstm.executeQuery();
            if (rs.next() == false) {
                return;
            }

            int current_albumId = rs.getInt("albumId");
            int imageId = rs.getInt("imageId");

            pstm = conn.prepareStatement("DELETE FROM Images WHERE imageId=?");
            pstm.setInt(1, imageId);

            pstm.executeUpdate();

            boolean emptyAlbum = checkEmptyAlbum(current_albumId);
            pstm.close();
            if (emptyAlbum == true) {
                deleteAlbum(current_albumId);
                if(current_albumId==albumId){
                    reset();
                }
            } else {
                return;
            }
        } catch (Exception e) {
            throw e;
        }
    }

    @Override
    public int getImageId(String link) throws Exception {
        try {
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
            Connection conn = DriverManager.getConnection(connectionString, serverUserName, serverPasswd);

            PreparedStatement pstm = conn.prepareStatement("SELECT * FROM Images WHERE link=?");
            pstm.setString(1, link);
            ResultSet rs = pstm.executeQuery();
            if (rs.next() == false) {
                return -1;
            }

            int imageId = rs.getInt("imageId");
            pstm.close();
            return imageId;
        } catch (Exception e) {
            throw e;
        }
    }

    @Override
    public void updateImageTag(String pictureTag, int imageId) throws Exception {
        try {
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
            Connection conn = DriverManager.getConnection(connectionString, serverUserName, serverPasswd);

            PreparedStatement pstm = conn.prepareStatement("UPDATE Images SET tag=? WHERE imageId=?");
            pstm.setString(1, pictureTag);
            pstm.setInt(2, imageId);
            pstm.executeUpdate();
            pstm.close();

        } catch (Exception e) {
            throw e;
        }
    }

    @Override
    public void updateImageName(String pictureName, int imageId) throws Exception {
        try {
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
            Connection conn = DriverManager.getConnection(connectionString, serverUserName, serverPasswd);

            PreparedStatement pstm = conn.prepareStatement("UPDATE Images SET imageName=? WHERE imageId=?");
            pstm.setString(1, pictureName);
            pstm.setInt(2, imageId);
            pstm.executeUpdate();
            pstm.close();

        } catch (Exception e) {
            throw e;
        }
    }

    public void updateOldThumbnail(int albumId) throws Exception {
        try {
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
            Connection conn = DriverManager.getConnection(connectionString, serverUserName, serverPasswd);

            PreparedStatement pstm = conn.prepareStatement("SELECT max(Images.imageId) as id FROM Images,Albums WHERE Albums.albumId= Images.albumId AND Albums.albumId=?");
            pstm.setInt(1, albumId);
            ResultSet rs = pstm.executeQuery();
            if (rs.next() == false) {
                System.out.println("test: 371");
                System.out.println("updateOldThumbnail");
                throw new Exception();
            }
            System.out.println("test: 374");
            int id=Integer.parseInt(rs.getString("id"));
            System.out.println("test: 376");
            pstm = conn.prepareStatement("SELECT link FROM Images WHERE imageId=?");
            System.out.println("test: 379");
            pstm.setInt(1, id);
            rs=pstm.executeQuery();
            if(rs.next()==false){
                System.out.println("test: 383");
                throw  new Exception();
            }
            String link=rs.getString("link");
            System.out.println(link);
            pstm = conn.prepareStatement("UPDATE Albums SET thumbnail=? WHERE albumId=?");
            pstm.setString(1, link);
            pstm.setInt(2, albumId);
            pstm.executeUpdate();
            
            pstm.close();
        } catch (Exception e) {
            throw e;
        }
    }

    @Override
    public void updateImageToExistAlbum(int albumId, int imageId) throws Exception {
        try {
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
            Connection conn = DriverManager.getConnection(connectionString, serverUserName, serverPasswd);

            PreparedStatement pstm = conn.prepareStatement("SELECT * FROM Images WHERE imageId=?");
            pstm.setInt(1, imageId);
            ResultSet rs = pstm.executeQuery();
            if (rs.next() == false) {
                throw new Exception();
            }
            int oldAlbumId = rs.getInt("albumId");
            String link = rs.getString("link");

            pstm = conn.prepareStatement("UPDATE Images SET albumId=? WHERE imageId=?");
            pstm.setInt(1, albumId);
            pstm.setInt(2, imageId);
            pstm.executeUpdate();
            updateThumbnail(link, albumId);

            boolean empty = checkEmptyAlbum(oldAlbumId);
            if (empty == true) {
                deleteAlbum(oldAlbumId);
            } else {
                updateOldThumbnail(oldAlbumId);
            }
            pstm.close();
        } catch (Exception e) {
            throw e;
        }
    }

    @Override
    public void updateImageToNewAlbum(String albumName, int imageId,int userId) throws Exception {
        try {
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
            Connection conn = DriverManager.getConnection(connectionString, serverUserName, serverPasswd);

            PreparedStatement pstm = conn.prepareStatement("SELECT Images.albumId, Albums.albumName,Images.link FROM Images,Albums WHERE Albums.albumId=Images.albumId AND Images.imageId=?");
            pstm.setInt(1, imageId);
            ResultSet rs = pstm.executeQuery();
            if (rs.next() == false) {
                throw new Exception();
            }

            String oldAlbumName = rs.getString("albumName");
            int oldAlbumId = rs.getInt("albumId");
            String link=rs.getString("link");

            if (oldAlbumName.equals(albumName) == false) {
                if (tempName==null) {
                    tempName = albumName;
                    Date calendar=new Date();
                    int year=calendar.getYear()+1990;
                    int month=calendar.getMonth();
                    int date=calendar.getDate();
                    String createdDate=year+"-"+month+"-"+date;
                    tempNewAlbumId=insertNewAlbum(albumName, userId, createdDate);
                }
                              
                pstm = conn.prepareStatement("UPDATE Images SET albumId=? WHERE imageId=?");    
                pstm.setInt(1, tempNewAlbumId);
                pstm.setInt(2, imageId);
                pstm.executeUpdate();
                updateThumbnail(link, tempNewAlbumId);
                
                boolean empty = checkEmptyAlbum(oldAlbumId);

                if (empty == true) {
                    deleteAlbum(oldAlbumId);
                }
                else{
                    updateOldThumbnail(oldAlbumId);
                }              
            }
            pstm.close();
        } catch (Exception e) {
            throw e;
        }
    }

    @Override
    public void reset() {
        createdNew=false;
        tempName=null;
    }
}
